package com.junzhiit.nengji.shop.dao.impl;

import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.bainaeco.framework.base.dao.AbstractBaseDAO;
import com.bainaeco.framework.common.jdbc.StatementParameter;
import com.bainaeco.framework.enums.IsDelete;
import com.junzhiit.nengji.shop.bo.GoodsBO;
import com.junzhiit.nengji.shop.bo.ModulesGoodsBO;
import com.junzhiit.nengji.shop.bo.ModulesGoodsListBO;
import com.junzhiit.nengji.shop.dao.ModulesGoodsDAO;
import com.junzhiit.nengji.shop.model.Goods;
import com.junzhiit.nengji.shop.model.ModulesGoods;
import com.junzhiit.nengji.shop.model.enums.AreaLevel;
import com.junzhiit.nengji.shop.model.enums.GoodsImagesType;
import com.junzhiit.nengji.shop.model.enums.GoodsStatus;
import com.junzhiit.nengji.shop.model.enums.Is;
import com.junzhiit.nengji.shop.model.enums.ModulesGoodsStatus;
import com.junzhiit.nengji.shop.model.enums.ShopIsPlatform;
import com.junzhiit.nengji.shop.model.enums.ShopStatus;
import com.junzhiit.nengji.shop.model.enums.TypeLevel;

@Repository
public class ModulesGoodsDAOImpl extends AbstractBaseDAO<ModulesGoods> implements ModulesGoodsDAO {

	@Override
	public List<ModulesGoodsBO> findAll(ModulesGoodsListBO modulesGoodsListBO, int pageNumber, int maxPageRow) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select");
		sql.append(" s.`name` shopName,g.profit,g.is_open_rebate,g.price,g.reference_price,g.is_open_sku,g.postage_type,");
		sql.append(" g.postage_price,g.type,g.stock,");
		
		//price by sku
		sql.append(" (case g.is_open_sku ");
		sql.append(" when 1 then (select case min(sc.price)=max(sc.price) when 0 then concat_ws('~',min(sc.price),max(sc.price)) when 1 then min(sc.price)  end  from sku_content sc where sc.goods_id=g.id) ");
		sql.append(" end)");
		sql.append(" sku_range_price,");
		
		sql.append(" mg.goods_id,mg.modules_id,mg.id,mg.sort,mg.`status`,g.`name` goods_name,g.status goodsStatus,g.is_help_peasant isHelpPeasant ");
		sql.append(" ,gi.file_name goods_pic,g.is_open_sell,g.discount");
		sql.append(" from modules_goods mg");
		sql.append(" left join goods g on  mg.goods_id=g.id");
		sql.append(" left join goods_images gi on  gi.goods_id=g.id and gi.type=?");
		sql.append(" inner join shop s on g.shop_id=s.id");
		sql.append(" where mg.is_delete=? and mg.modules_id=?");

		StatementParameter param = new StatementParameter();
		param.setInt(GoodsImagesType.COVER.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setString(modulesGoodsListBO.getModulesId());

		if (StringUtils.isNotBlank(modulesGoodsListBO.getGoodsName())) {
			sql.append(" and g.`name` like ? ");
			param.setString("%" + modulesGoodsListBO.getGoodsName() + "%");
		}
		if (modulesGoodsListBO.getStatus() != null) {
			sql.append(" and mg.status=?");
			param.setInt(modulesGoodsListBO.getStatus());
		}

		sql.append(" order by mg.sort desc");

		return super.queryForPagingList(sql.toString(), ModulesGoodsBO.class, param, pageNumber, maxPageRow);
	}

	@Override
	public int getTotalCountByAll(ModulesGoodsListBO modulesGoodsListBO) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select");
		sql.append(" count(*)");
		sql.append(" from modules_goods mg");
		sql.append(" left join goods g on  mg.goods_id=g.id");
		sql.append(" left join goods_images gi on  gi.goods_id=g.id and gi.type=?");
		sql.append(" inner join shop s on g.shop_id=s.id");
		sql.append(" where mg.is_delete=? and mg.modules_id=?");

		StatementParameter param = new StatementParameter();
		param.setInt(GoodsImagesType.COVER.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setString(modulesGoodsListBO.getModulesId());

		if (StringUtils.isNotBlank(modulesGoodsListBO.getGoodsName())) {
			sql.append(" and g.`name` like ? ");
			param.setString("%" + modulesGoodsListBO.getGoodsName() + "%");
		}
		if (modulesGoodsListBO.getStatus() != null) {
			sql.append(" and mg.status=?");
			param.setInt(modulesGoodsListBO.getStatus());
		}

		return super.queryForInt(sql.toString(), param);
	}

	@Override
	public int getTotalCount(String id) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select count(id) from modules_goods");
		sql.append(" where is_delete=? and modules_id=?");

		StatementParameter param = new StatementParameter();
		param.setInt(IsDelete.NO.toIntValue());
		param.setString(id);

		return super.queryForInt(sql.toString(), param);
	}

	@Override
	public int queryGoodsIsExist(String goodsId, String modulesId) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select count(id) from modules_goods");
		sql.append(" where is_delete=?");
		sql.append(" and modules_id=?");
		sql.append(" and goods_id=?");

		StatementParameter param = new StatementParameter();
		param.setInt(IsDelete.NO.toIntValue());
		param.setString(modulesId);
		param.setString(goodsId);

		return queryForInt(sql.toString(), param);
	}

	@Override
	public int getMaxSort(String id) {

		StringBuffer sql = new StringBuffer();
		sql.append(" select ifnull(max(sort),0) from modules_goods");
		sql.append(" where is_delete=? and modules_id=?");

		StatementParameter param = new StatementParameter();
		param.setInt(IsDelete.NO.toIntValue());
		param.setString(id);

		return super.queryForInt(sql.toString(), param);
	}

	@Override
	public List<GoodsBO> findGoodsPageAll(Goods goods, String modulesId, int pageNumber, int maxPageRow) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select ");
		sql.append(
				" g.`code`,g.id,g.`name`,g.price,g.reference_price,g.stock,g.is_open_sku,g.is_open_member,g.is_free_shipping,g.`status`,g.is_recommend,g.is_open_rebate,g.profit,");
		sql.append(" b.`name` brand_name, ");
		sql.append(" t.`name` type_name, ");
		sql.append(" s.`name` shop_name,g.is_open_sell,g.discount,g.is_help_peasant isHelpPeasant ");
		sql.append(" ,gi.file_name ");
		sql.append("  from goods g ");
		sql.append(" left join brand b on g.brand_id=b.id ");
		sql.append(" left join type t on g.type_id=t.id and t.`level`=? ");
		sql.append(" left join goods_images gi on g.id=gi.goods_id and gi.type=? ");
		sql.append(" inner join shop s on g.shop_id=s.id");
		sql.append(" where g.is_delete=? ");

		StatementParameter param = new StatementParameter();
		param.setInt(TypeLevel.THREE.toIntValue());
		param.setInt(GoodsImagesType.COVER.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());

		if (StringUtils.isNotBlank(goods.getShopId())) {
			sql.append(" and g.shop_id=? ");
			param.setString(goods.getShopId());
		}else{
			sql.append(" and s.is_platform=? ");
			param.setInt(ShopIsPlatform.HEADQUARTERS.toIntStatus());
		}

		if (StringUtils.isNotBlank(goods.getName())) {
			sql.append(" and g.name like ? ");
			param.setString("%" + goods.getName() + "%");
		}

		if (StringUtils.isNotBlank(goods.getBrandId())) {
			sql.append(" and g.brand_id=? ");
			param.setString(goods.getBrandId());
		}

		if (StringUtils.isNotBlank(goods.getTypeId())) {
			sql.append(" and g.type_id=? ");
			param.setString(goods.getTypeId());
		}

		if (goods.getStatus() != null) {
			sql.append(" and g.status=? ");
			param.setInt(goods.getStatus());
		}

		if (goods.getIsRecommend() != null) {
			sql.append(" and g.is_recommend=? ");
			param.setInt(goods.getIsRecommend());
		}
		
		if(goods.getIsHelpPeasant() !=null){
			sql.append(" and g.is_help_peasant = ? ");
			param.setInt(goods.getIsHelpPeasant());
		}

		if (StringUtils.isNotBlank(modulesId)) {
			sql.append(" and g.id not in (select goods_id from modules_goods where modules_id=?)");
			param.setString(modulesId);
		}

		sql.append(" order by g.created_time desc");

		return super.queryForPagingList(sql.toString(), GoodsBO.class, param, pageNumber, maxPageRow);
	}

	@Override
	public int getGoodsTotalCount(Goods goods, String modulesId) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select count(g.id) from goods g ");
		sql.append(" left join brand b on g.brand_id=b.id ");
		sql.append(" left join type t on g.type_id=t.id and t.`level`=? ");
		sql.append(" left join goods_images gi on g.id=gi.goods_id and gi.type=? ");
		sql.append(" inner join shop s on g.shop_id=s.id");
		sql.append(" where g.is_delete=? ");

		StatementParameter param = new StatementParameter();
		param.setInt(TypeLevel.THREE.toIntValue());
		param.setInt(GoodsImagesType.COVER.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());

		if (StringUtils.isNotBlank(goods.getShopId())) {
			sql.append(" and g.shop_id=? ");
			param.setString(goods.getShopId());
		}else{
			sql.append(" and s.is_platform=? ");
			param.setInt(ShopIsPlatform.HEADQUARTERS.toIntStatus());
		}

		if (StringUtils.isNotEmpty(goods.getName())) {
			sql.append(" and g.name like ? ");
			param.setString("%" + goods.getName() + "%");
		}

		if (StringUtils.isNotEmpty(goods.getBrandId())) {
			sql.append(" and g.brand_id=? ");
			param.setString(goods.getBrandId());
		}

		if (StringUtils.isNotEmpty(goods.getTypeId())) {
			sql.append(" and g.type_id=? ");
			param.setString(goods.getTypeId());
		}

		if (goods.getStatus() != null) {
			sql.append(" and g.status=? ");
			param.setInt(goods.getStatus());
		}

		if (goods.getIsRecommend() != null) {
			sql.append(" and g.is_recommend=? ");
			param.setInt(goods.getIsRecommend());
		}
		
		if(goods.getIsHelpPeasant() !=null){
			sql.append(" and g.is_help_peasant = ? ");
			param.setInt(goods.getIsHelpPeasant());
		}

		if (StringUtils.isNotBlank(modulesId)) {
			sql.append(" and g.id not in (select goods_id from modules_goods where modules_id=?)");
			param.setString(modulesId);
		}

		return super.queryForInt(sql.toString(), param);
	}

	@Override
	public ModulesGoods getPreModulesGoods(ModulesGoods modulesGoods) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from modules_goods ");
		sql.append(" where is_delete=?");
		sql.append(" and modules_id=?");
		sql.append(" and sort<(select sort from modules_goods where id=?)");
		sql.append(" order by sort desc limit 1");

		StatementParameter param = new StatementParameter();
		param.setInt(IsDelete.NO.toIntValue());
		param.setString(modulesGoods.getModulesId());
		param.setString(modulesGoods.getId());

		return super.query(sql.toString(), ModulesGoods.class, param);
	}

	@Override
	public ModulesGoods getNextModulesGoods(ModulesGoods modulesGoods) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from modules_goods ");
		sql.append(" where is_delete=?");
		sql.append(" and modules_id=?");
		sql.append(" and sort>(select sort from modules_goods where id=?)");
		sql.append(" order by sort asc limit 1");

		StatementParameter param = new StatementParameter();
		param.setInt(IsDelete.NO.toIntValue());
		param.setString(modulesGoods.getModulesId());
		param.setString(modulesGoods.getId());

		return super.query(sql.toString(), ModulesGoods.class, param);
	}

	@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
	@Override
	public boolean batchUpdateStatus(String[] ids, Integer status) {
		final StringBuffer sql = new StringBuffer();
		final StatementParameter param = new StatementParameter();
		sql.append("update modules_goods set status=? where 1=1 and");

		if (ids.length > 0) {

			sql.append(" id in (");
			param.setInt(status);
			for (int i = 0, len = ids.length; i < ids.length; i++) {
				sql.append("?");
				if (i < len - 1) {
					sql.append(",");
				}
				param.setString(ids[i]);
			}
			sql.append(")");

		}

		return super.updateForBoolean(sql.toString(), param);
	}

	@Override
	public List<ModulesGoodsBO> findModulesGoods(String columnCode, String cityCode,String shopId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select mg.modules_id,m.name 'modules_name',m.code 'modules_code',");
		sql.append("mg.goods_id,mg.status,mg.sort,g.`code`,g.hint,g.shop_id,");
		sql.append("g.`name` 'goods_name',g.reference_price,g.stock,g.is_open_sku,mg.id,");
		sql.append("(case g.is_open_sku when ? then g.price when ? then (select min(sc.price)");
		sql.append(" from sku_content sc where sc.is_delete = ? and sc.goods_id=g.id)end) price,");
		sql.append("b.`name` brand_name,t.`name` 'type_name',s.`name` 'shop_name',s.shop_head_img_url,");
		sql.append("g.is_open_member,g.is_free_shipping,g.is_recommend,g.is_open_rebate,g.is_help_peasant isHelpPeasant,m.is_recommand_goods,");
		sql.append("gi.file_name 'goods_pic',c.id 'column_id',c.code 'column_code',c.name 'column_name',m.goods_quantity,g.is_open_sell,g.discount");
		sql.append(" from modules_goods mg");
		sql.append(" inner join modules m on mg.modules_id=m.id ");
		sql.append(" inner join `column` c on c.id=m.column_id");
		sql.append(" inner join goods g on mg.goods_id=g.id and g.status=?");
		sql.append(" left join brand b on g.brand_id=b.id");
		sql.append(" left join type t on g.type_id=t.id");
		sql.append(" left join goods_images gi on g.id=gi.goods_id and gi.is_delete=? and gi.type = case when m.is_recommand_goods=? then ? else ? end");
		sql.append(" left join shop s on g.shop_id=s.id and s.status<>?");
		sql.append(" left join area ar on s.area_city_id=ar.id");
		sql.append(" where mg.is_delete=? and mg.status=? and g.is_delete=? and g.status =?");
		
		StatementParameter param = new StatementParameter();
		param.setInt(Is.NO.toIntValue());
		param.setInt(Is.YES.toIntValue());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(GoodsStatus.ACTIVE.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(Is.NO.toIntValue());
		param.setInt(GoodsImagesType.COVER.toIntStatus());
		param.setInt(GoodsImagesType.COMMEND.toIntStatus());
		param.setInt(ShopStatus.STOP.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(ModulesGoodsStatus.ACTIVE.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(GoodsStatus.ACTIVE.toIntStatus());

		if(StringUtils.isNotBlank(columnCode)){
			sql.append(" and c.code=?");
			param.setString(columnCode);
		}
		
		if(StringUtils.isNoneBlank(shopId)){
			sql.append(" and c.shop_id=?");
			param.setString(shopId);
		}else{
			sql.append(" and isnull(m.shop_id)");
		}
		
		if(StringUtils.isNotBlank(cityCode)){
			sql.append(" and ar.city_code=? and ar.level=?");
			param.setString(cityCode);
			param.setInt(AreaLevel.ONE.toIntValue());
		}
		
		sql.append(" order by g.is_open_rebate desc,g.sales_volume desc,mg.sort asc");	
		return queryForList(sql.toString(), ModulesGoodsBO.class, param);
	}
	
	@Override
	public int getModulesGoodsTotalCount(String columnCode, String cityCode,String shopId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*) ");
		sql.append(" from modules_goods mg");
		sql.append(" inner join modules m on mg.modules_id=m.id ");
		sql.append(" inner join `column` c on c.id=m.column_id");
		sql.append(" inner join goods g on mg.goods_id=g.id and g.status=?");
		sql.append(" left join brand b on g.brand_id=b.id");
		sql.append(" left join type t on g.type_id=t.id");
		sql.append(" left join goods_images gi on g.id=gi.goods_id and gi.is_delete=? and gi.type = case when m.is_recommand_goods=? then ? else ? end");
		sql.append(" left join shop s on g.shop_id=s.id and s.status<>?");
		sql.append(" left join area ar on s.area_city_id=ar.id");
		sql.append(" where mg.is_delete=? and mg.status=? and g.is_delete=? and g.status =?");
		
		StatementParameter param = new StatementParameter();
		
		param.setInt(GoodsStatus.ACTIVE.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(Is.NO.toIntValue());
		param.setInt(GoodsImagesType.COVER.toIntStatus());
		param.setInt(GoodsImagesType.COMMEND.toIntStatus());
		param.setInt(ShopStatus.STOP.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(ModulesGoodsStatus.ACTIVE.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(GoodsStatus.ACTIVE.toIntStatus());

		if(StringUtils.isNotBlank(columnCode)){
			sql.append(" and c.code=?");
			param.setString(columnCode);
		}
		
		if(StringUtils.isNoneBlank(shopId)){
			sql.append(" and c.shop_id=?");
			param.setString(shopId);
		}else{
			sql.append(" and isnull(m.shop_id)");
		}
		
		if(StringUtils.isNotBlank(cityCode)){
			sql.append(" and ar.city_code=? and ar.level=?");
			param.setString(cityCode);
			param.setInt(AreaLevel.ONE.toIntValue());
		}
		
		sql.append(" order by g.is_open_rebate desc,g.sales_volume desc,mg.sort asc");	
		return queryForInt(sql.toString(), param);
	}
	
	@Override
	public List<ModulesGoodsBO> findModulesGoodsBymodulesId(String modulesId, String cityCode,String shopId,Integer pageNumber,Integer maxPageRow) {
		StringBuffer sql = new StringBuffer();
		sql.append("select mg.modules_id,m.name 'modules_name',m.code 'modules_code',");
		sql.append("mg.goods_id,mg.status,mg.sort,g.`code`,g.hint,g.shop_id,");
		sql.append("g.`name` 'goods_name',g.reference_price,g.stock,g.is_open_sku,mg.id,");
		sql.append("(case g.is_open_sku when ? then g.price when ? then (select min(sc.price)");
		sql.append(" from sku_content sc where sc.is_delete = ? and sc.goods_id=g.id)end) price,");
		sql.append("b.`name` brand_name,t.`name` 'type_name',s.`name` 'shop_name',s.shop_head_img_url,");
		sql.append("g.is_open_member,g.is_free_shipping,g.is_recommend,g.is_open_rebate,g.is_help_peasant isHelpPeasant,m.is_recommand_goods,");
		sql.append("gi.file_name 'goods_pic',c.id 'column_id',c.code 'column_code',c.name 'column_name',m.goods_quantity,g.is_open_sell,g.discount");
		sql.append(" from modules_goods mg");
		sql.append(" inner join modules m on mg.modules_id=m.id ");
		sql.append(" inner join `column` c on c.id=m.column_id");
		sql.append(" inner join goods g on mg.goods_id=g.id and g.status=?");
		sql.append(" left join brand b on g.brand_id=b.id");
		sql.append(" left join type t on g.type_id=t.id");
		sql.append(" left join goods_images gi on g.id=gi.goods_id and gi.is_delete=? and gi.type = case when m.is_recommand_goods=? then ? else ? end");
		sql.append(" left join shop s on g.shop_id=s.id and s.status<>?");
		sql.append(" left join area ar on s.area_city_id=ar.id");
		sql.append(" where mg.is_delete=? and mg.status=? and g.is_delete=? and g.status =?");
		
		StatementParameter param = new StatementParameter();
		param.setInt(Is.NO.toIntValue());
		param.setInt(Is.YES.toIntValue());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(GoodsStatus.ACTIVE.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(Is.NO.toIntValue());
		param.setInt(GoodsImagesType.COVER.toIntStatus());
		param.setInt(GoodsImagesType.COMMEND.toIntStatus());
		param.setInt(ShopStatus.STOP.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(ModulesGoodsStatus.ACTIVE.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(GoodsStatus.ACTIVE.toIntStatus());

		if(StringUtils.isNotBlank(modulesId)){
			sql.append(" and m.id=?");
			param.setString(modulesId);
		}
		
		if(StringUtils.isNoneBlank(shopId)){
			sql.append(" and c.shop_id=?");
			param.setString(shopId);
		}else{
			sql.append(" and isnull(m.shop_id)");
		}
		
		if(StringUtils.isNotBlank(cityCode)){
			sql.append(" and ar.city_code=? and ar.level=?");
			param.setString(cityCode);
			param.setInt(AreaLevel.ONE.toIntValue());
		}
		
		sql.append(" order by g.is_open_rebate desc,g.sales_volume desc,mg.sort asc");	
		return queryForPagingList(sql.toString(), ModulesGoodsBO.class, param,pageNumber,maxPageRow);
	}
	
	//这个方法属于shop项目.
	@Override
	public List<ModulesGoodsBO> findModulesGoodsBelongToShop(String columnCode, String cityCode,String shopId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select mg.modules_id,m.name 'modules_name',m.code 'modules_code',");
		sql.append("mg.goods_id,mg.status,mg.sort,g.`code`,g.hint,g.shop_id,");
		sql.append("g.`name` 'goods_name',g.reference_price,g.stock,g.is_open_sku,mg.id,");
		sql.append("(case g.is_open_sku when ? then g.price when ? then (select min(sc.price)");
		sql.append(" from sku_content sc where sc.is_delete = ? and sc.goods_id=g.id)end) price,");
		sql.append("b.`name` brand_name,t.`name` 'type_name',s.`name` 'shop_name',s.shop_head_img_url,");
		sql.append("g.is_open_member,g.is_free_shipping,g.is_recommend,g.is_open_rebate,g.is_help_peasant isHelpPeasant,m.is_recommand_goods,");
		sql.append("gi.file_name 'goods_pic',c.id 'column_id',c.code 'column_code',c.name 'column_name',m.goods_quantity,g.is_open_sell,g.discount");
		sql.append(" from modules_goods mg");
		sql.append(" inner join modules m on mg.modules_id=m.id ");
		sql.append(" inner join `column` c on c.id=m.column_id");
		sql.append(" inner join goods g on mg.goods_id=g.id and g.status=?");
		sql.append(" left join brand b on g.brand_id=b.id");
		sql.append(" left join type t on g.type_id=t.id");
		sql.append(" left join goods_images gi on g.id=gi.goods_id and gi.is_delete=? and gi.type = case when m.is_recommand_goods=? then ? else ? end");
		sql.append(" left join shop s on g.shop_id=s.id and s.status<>?");
		sql.append(" left join area ar on s.area_city_id=ar.id");
		sql.append(" where mg.is_delete=? and mg.status=? and g.is_delete=? and g.status =?");
		
		StatementParameter param = new StatementParameter();
		param.setInt(Is.NO.toIntValue());
		param.setInt(Is.YES.toIntValue());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(GoodsStatus.ACTIVE.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(Is.NO.toIntValue());
		param.setInt(GoodsImagesType.COVER.toIntStatus());
		param.setInt(GoodsImagesType.COMMEND.toIntStatus());
		param.setInt(ShopStatus.STOP.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(ModulesGoodsStatus.ACTIVE.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(GoodsStatus.ACTIVE.toIntStatus());

		if(StringUtils.isNotBlank(columnCode)){
			sql.append(" and c.code=?");
			param.setString(columnCode);
		}
		
		if(StringUtils.isNoneBlank(shopId)){
			sql.append(" and c.shop_id=?");
			param.setString(shopId);
		}
		
		if(StringUtils.isNotBlank(cityCode)){
			sql.append(" and ar.city_code=? and ar.level=?");
			param.setString(cityCode);
			param.setInt(AreaLevel.ONE.toIntValue());
		}
		
		sql.append(" order by mg.sort desc");	
		return queryForList(sql.toString(), ModulesGoodsBO.class, param);
	}
	
	@Override
	public List<ModulesGoodsBO> findModulesGoodsByModulesCode(String modulesCode, String cityCode,String shopId,Integer pageNumber,Integer maxPageRow) {
		StringBuffer sql = new StringBuffer();
		sql.append("select mg.modules_id,m.name 'modules_name',m.code 'modules_code',");
		sql.append("mg.goods_id,mg.status,mg.sort,g.`code`,g.hint,g.shop_id,");
		sql.append("g.`name` 'goods_name',g.reference_price,g.stock,g.is_open_sku,mg.id,");
		sql.append("(case g.is_open_sku when ? then g.price when ? then (select min(sc.price)");
		sql.append(" from sku_content sc where sc.is_delete = ? and sc.goods_id=g.id)end) price,");
		sql.append("b.`name` brand_name,t.`name` 'type_name',s.`name` 'shop_name',s.shop_head_img_url,");
		sql.append("g.is_open_member,g.is_free_shipping,g.is_recommend,g.is_open_rebate,g.is_help_peasant isHelpPeasant,m.is_recommand_goods,");
		sql.append("gi.file_name 'goods_pic',c.id 'column_id',c.code 'column_code',c.name 'column_name',m.goods_quantity,g.is_open_sell,g.discount");
		sql.append(" from modules_goods mg");
		sql.append(" inner join modules m on mg.modules_id=m.id ");
		sql.append(" inner join `column` c on c.id=m.column_id");
		sql.append(" inner join goods g on mg.goods_id=g.id and g.status=?");
		sql.append(" left join brand b on g.brand_id=b.id");
		sql.append(" left join type t on g.type_id=t.id");
		sql.append(" left join goods_images gi on g.id=gi.goods_id and gi.is_delete=? and gi.type = case when m.is_recommand_goods=? then ? else ? end");
		sql.append(" left join shop s on g.shop_id=s.id and s.status<>?");
		sql.append(" left join area ar on s.area_city_id=ar.id");
		sql.append(" where mg.is_delete=? and mg.status=? and g.is_delete=? and g.status =?");
		
		StatementParameter param = new StatementParameter();
		param.setInt(Is.NO.toIntValue());
		param.setInt(Is.YES.toIntValue());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(GoodsStatus.ACTIVE.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(Is.NO.toIntValue());
		param.setInt(GoodsImagesType.COVER.toIntStatus());
		param.setInt(GoodsImagesType.COMMEND.toIntStatus());
		param.setInt(ShopStatus.STOP.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(ModulesGoodsStatus.ACTIVE.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(GoodsStatus.ACTIVE.toIntStatus());

		if(StringUtils.isNotBlank(modulesCode)){
			sql.append(" and m.code=?");
			param.setString(modulesCode);
		}
		
		if(StringUtils.isNoneBlank(shopId)){
			sql.append(" and c.shop_id=?");
			param.setString(shopId);
		}
		
		if(StringUtils.isNotBlank(cityCode)){
			sql.append(" and ar.city_code=? and ar.level=?");
			param.setString(cityCode);
			param.setInt(AreaLevel.ONE.toIntValue());
		}
		
		sql.append(" order by g.is_open_rebate desc,g.sales_volume desc,mg.sort desc");
		return queryForPagingList(sql.toString(), ModulesGoodsBO.class, param,pageNumber,maxPageRow);
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
	public boolean deleteByGoodsId(String id){
		StringBuffer sql=new StringBuffer();
		sql.append("delete from modules_goods where goods_id=?");
		
		StatementParameter param=new StatementParameter();
		param.setString(id);
		
		return super.updateForBoolean(sql.toString(), param);
	}

	@Override
	public List<ModulesGoodsBO> findModulesGoods(String columnCode, String cityCode,String modulesCode, Integer pageNumber,
			Integer maxPageRow) {
		StringBuffer sql = new StringBuffer();
		sql.append("select mg.modules_id,m.name 'modules_name',m.code 'modules_code',");
		sql.append("mg.goods_id,mg.status,mg.sort,g.`code`,g.hint,g.shop_id,");
		sql.append("g.`name` 'goods_name',g.reference_price,g.stock,g.is_open_sku,mg.id,");
		sql.append("(case g.is_open_sku when ? then g.price when ? then (select min(sc.price)");
		sql.append(" from sku_content sc where sc.is_delete = ? and sc.goods_id=g.id)end) price,");
		sql.append("b.`name` brand_name,t.`name` 'type_name',s.`name` 'shop_name',s.shop_head_img_url,");
		sql.append("g.is_open_member,g.is_free_shipping,g.is_recommend,g.is_open_rebate,g.is_help_peasant isHelpPeasant,m.is_recommand_goods,");
		sql.append("gi.file_name 'goods_pic',c.id 'column_id',c.code 'column_code',c.name 'column_name',m.goods_quantity,g.is_open_sell,g.discount");
		sql.append(" from modules_goods mg");
		sql.append(" inner join modules m on mg.modules_id=m.id ");
		sql.append(" inner join `column` c on c.id=m.column_id");
		sql.append(" inner join goods g on mg.goods_id=g.id and g.status=?");
		sql.append(" left join brand b on g.brand_id=b.id");
		sql.append(" left join type t on g.type_id=t.id");
		sql.append(" left join goods_images gi on g.id=gi.goods_id and gi.is_delete=? and gi.type = case when m.is_recommand_goods=? then ? else ? end");
		sql.append(" left join shop s on g.shop_id=s.id and s.status<>?");
		sql.append(" left join area ar on s.area_city_id=ar.id");
		sql.append(" where mg.is_delete=? and mg.status=? and g.is_delete=? and g.status =?");
		
		StatementParameter param = new StatementParameter();
		param.setInt(Is.NO.toIntValue());
		param.setInt(Is.YES.toIntValue());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(GoodsStatus.ACTIVE.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(Is.NO.toIntValue());
		param.setInt(GoodsImagesType.COVER.toIntStatus());
		param.setInt(GoodsImagesType.COMMEND.toIntStatus());
		param.setInt(ShopStatus.STOP.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(ModulesGoodsStatus.ACTIVE.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(GoodsStatus.ACTIVE.toIntStatus());
		
		if(StringUtils.isNotBlank(modulesCode)){
			sql.append(" and m.code=?");
			param.setString(modulesCode);
		}
		if(StringUtils.isNotBlank(columnCode)){
			sql.append(" and c.code=?");
			param.setString(columnCode);
		}
		if(StringUtils.isNotBlank(cityCode)){
			sql.append(" and ar.city_code=? and ar.level=?");
			param.setString(cityCode);
			param.setInt(AreaLevel.ONE.toIntValue());
		}
		
		sql.append(" order by g.is_open_rebate desc,g.sales_volume desc,mg.sort asc");	
		return queryForPagingList(sql.toString(), ModulesGoodsBO.class, param, pageNumber,maxPageRow);
	}

	@Override
	public Integer getTotalCount(String columnCode, String cityCode,String modulesCode) {
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*)");
		sql.append(" from modules_goods mg");
		sql.append(" inner join modules m on mg.modules_id=m.id ");
		sql.append(" inner join `column` c on c.id=m.column_id");
		sql.append(" inner join goods g on mg.goods_id=g.id and g.status=?");
		sql.append(" left join shop s on g.shop_id=s.id and s.status<>?");
		sql.append(" left join area ar on s.area_city_id=ar.id");
		sql.append(" where mg.is_delete=? and mg.status=? and g.is_delete=? and g.status =?");
		
		StatementParameter param = new StatementParameter();
		param.setInt(GoodsStatus.ACTIVE.toIntStatus());
		param.setInt(ShopStatus.STOP.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(ModulesGoodsStatus.ACTIVE.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(GoodsStatus.ACTIVE.toIntStatus());

		if(StringUtils.isNotBlank(modulesCode)){
			sql.append(" and m.code=?");
			param.setString(modulesCode);
		}
		if(StringUtils.isNotBlank(columnCode)){
			sql.append(" and c.code=?");
			param.setString(columnCode);
		}
		if(StringUtils.isNotBlank(cityCode)){
			sql.append(" and ar.city_code=? and ar.level=?");
			param.setString(cityCode);
			param.setInt(AreaLevel.ONE.toIntValue());
		}
		
		return queryForInt(sql.toString(), param);
	}
	
	@Override
	public int getTotalCountByGoodsId(String goodsId){
		StringBuffer sql=new StringBuffer();
		sql.append(" select * from modules_goods");
		sql.append(" where is_delete=?");
		sql.append(" and goods_id = ? ");
		
		StatementParameter param=new StatementParameter();
		param.setInt(IsDelete.YES.toIntValue());
		param.setString(goodsId);
		
		return queryForInt(sql.toString(), param);
	}
	
	@Override
	public List<ModulesGoodsBO> findByColumnCode(String columnCode) {
		StatementParameter param = new StatementParameter();
		StringBuffer sql = new StringBuffer();
		
		sql.append("SELECT mg.rownum, mg.modules_sort, mg.modules_name, mg.modules_code, mg.goods_pic, g.name goods_name, g.*, IF(g.is_open_sku = 1, IFNULL(mg.sc_price, g.price), g.price) price ");
		sql.append("FROM ( ");
		sql.append("	SELECT cg.id, cg.rownum, cg.modules_sort, cg.modules_name, cg.modules_code, cg.goods_pic, MIN(sc.price) sc_price ");
		sql.append("	FROM (  ");
		sql.append("	 	SELECT modules_sort, modules_name, modules_code, id, rownum, goods_pic  ");
		sql.append("	 	FROM (  ");
		sql.append("	 		SELECT IF(@sort != modules_sort, (@sort:=modules_sort+@rownum:=0) - modules_sort, @rownum:=@rownum+1) AS rownum, modules_sort, modules_name, modules_code, id, goods_pic ");
		sql.append("	 		FROM (  ");
		sql.append("	 			SELECT mg.modules_sort, mg.modules_name, mg.modules_code, g.id, gi.file_name goods_pic, @sort:=0, @rownum:=0 "); 
		sql.append("	 			FROM (  ");
		sql.append("	 				SELECT m.modules_name, m.modules_sort, m.modules_code, mg.goods_id, mg.sort "); 
		sql.append("	 				FROM (  ");
		sql.append("	 					SELECT m.id, m.name modules_name, m.sort modules_sort, m.code modules_code "); 
		sql.append("	 					FROM (  ");
		sql.append("	 						SELECT id, `name` "); 
		sql.append("	 						FROM `column`  ");
		sql.append("	 						WHERE `code` = ? ");
		sql.append("	 						AND IFNULL(shop_id, '') = '' "); 
		sql.append("	 					) c  ");
		sql.append("	 					JOIN modules m "); 
		sql.append("	 					ON c.id = m.column_id "); 
		sql.append("	 					AND m.is_delete = 0  ");
		sql.append("	 				) m   ");
		sql.append("	 				JOIN modules_goods mg "); 
		sql.append("	 				ON m.id = mg.modules_id  ");
		sql.append("	 				WHERE mg.is_delete = 0  ");
		sql.append("	 				AND mg.status = 1  ");
		sql.append("	 			) mg  ");
		sql.append("	 			JOIN goods g "); 
		sql.append("	 			ON mg.goods_id = g.id AND g.is_delete = 0 ");
		sql.append("	 			JOIN goods_images gi  ");
		sql.append("	 			ON mg.goods_id = gi.goods_id AND gi.type = 0 AND gi.is_delete = 0 ");
		sql.append("	 			ORDER BY mg.modules_sort ASC, g.is_open_rebate DESC, g.sales_volume DESC, mg.sort ASC "); 
		sql.append("	 		) g  ");
		sql.append("	 	) g  ");
		sql.append("	 	GROUP BY modules_sort, modules_name, modules_code, id, rownum, goods_pic "); 
		sql.append("	 	HAVING rownum < 20  ");
		sql.append("	) cg  ");
		sql.append("	LEFT JOIN sku_content sc ");
		sql.append("	ON cg.id = sc.goods_id ");
		sql.append("	AND sc.is_delete = 0 ");
		sql.append("	GROUP BY cg.id, cg.rownum, cg.modules_sort, cg.modules_name, cg.modules_code, cg.goods_pic ");
		sql.append(") mg ");
		sql.append("JOIN goods g "); 
		sql.append("ON mg.id = g.id ");
		sql.append("ORDER BY mg.modules_sort ASC, mg.rownum ASC ");
		
		param.setString(columnCode);
		
		return queryForList(sql.toString(), ModulesGoodsBO.class, param);
	}
	
	@Override
	public List<ModulesGoodsBO> findPaginationByCode(String columnCode, String modulesCode, Integer pageNumber, Integer maxPageRow) {
		StatementParameter param = new StatementParameter();
		StringBuffer sql = new StringBuffer();
		
		sql.append("SELECT mg.goods_pic, g.name goods_name, g.*, IF(g.is_open_sku = 1, IFNULL(mg.sc_price, g.price), g.price) price ");
		sql.append("FROM ( ");
		sql.append("	SELECT mg.id, mg.goods_pic, mg.is_open_rebate, mg.sales_volume, mg.sort, MIN(sc.price) sc_price ");
		sql.append("	FROM ( ");
		sql.append("		SELECT g.id, gi.file_name goods_pic, g.is_open_rebate, g.sales_volume, mg.sort ");
		sql.append("		FROM (  ");
		sql.append("			SELECT m.modules_code, mg.goods_id, mg.sort ");
		sql.append("			FROM (  ");
		sql.append("				SELECT m.id, m.name modules_name, m.code modules_code  ");
		sql.append("				FROM (  ");
		sql.append("					SELECT id, `name`  ");
		sql.append("					FROM `column`  ");
		sql.append("					WHERE `code` = ?  ");
		sql.append("					AND IFNULL(shop_id, '') = ''  ");
		sql.append("				) c  ");
		sql.append("				JOIN modules m  ");
		sql.append("				ON c.id = m.column_id  ");
		sql.append("				AND m.is_delete = 0  ");
		sql.append("				AND m.code = ? ");
		sql.append("			) m   ");
		sql.append("			JOIN modules_goods mg  ");
		sql.append("			ON m.id = mg.modules_id  ");
		sql.append("			WHERE mg.is_delete = 0  ");
		sql.append("			AND mg.status = 1  ");
		sql.append("		) mg  ");
		sql.append("		JOIN goods g  ");
		sql.append("		ON mg.goods_id = g.id AND g.is_delete = 0 ");
		sql.append("		JOIN goods_images gi  ");
		sql.append("		ON mg.goods_id = gi.goods_id AND gi.type = 0 AND gi.is_delete = 0 ");
		sql.append("		ORDER BY g.is_open_rebate DESC, g.sales_volume DESC, mg.sort ASC ");
		sql.append("		LIMIT ?, ? ");
		sql.append("	) mg ");
		sql.append("	LEFT JOIN sku_content sc ");
		sql.append("	ON mg.id = sc.goods_id AND sc.is_delete = 0 ");
		sql.append("	GROUP BY mg.id, mg.goods_pic, mg.is_open_rebate, mg.sales_volume, mg.sort ");
		sql.append(") mg ");
		sql.append("JOIN goods g  ");
		sql.append("ON mg.id = g.id ");
		sql.append("ORDER BY is_open_rebate DESC, sales_volume DESC, sort ASC ");
		
		param.setString(columnCode);
		param.setString(modulesCode);
		param.setInt(pageNumber);
		param.setInt(maxPageRow);
		
		return queryForList(sql.toString(), ModulesGoodsBO.class, param);
	}
	
	@Override
	public int getPaginationTotalCount(String columnCode, String modulesCode) {
		StatementParameter param = new StatementParameter();
		StringBuffer sql = new StringBuffer();
		
		sql.append("SELECT COUNT(1) ");
		sql.append("FROM (  ");
		sql.append("SELECT m.modules_code, mg.goods_id, mg.sort ");
		sql.append("	FROM (  ");
		sql.append("		SELECT m.id, m.name modules_name, m.code modules_code  ");
		sql.append("		FROM (  ");
		sql.append("			SELECT id, `name`  ");
		sql.append("			FROM `column`  ");
		sql.append("			WHERE `code` = ? ");
		sql.append("			AND IFNULL(shop_id, '') = ''  ");
		sql.append("		) c  ");
		sql.append("		JOIN modules m  ");
		sql.append("		ON c.id = m.column_id  ");
		sql.append("		AND m.is_delete = 0  ");
		sql.append("		AND m.code = ? ");
		sql.append("	) m   ");
		sql.append("	JOIN modules_goods mg  ");
		sql.append("	ON m.id = mg.modules_id  ");
		sql.append("	WHERE mg.is_delete = 0  ");
		sql.append("	AND mg.status = 1  ");
		sql.append(") mg  ");
		sql.append("JOIN goods g  ");
		sql.append("ON mg.goods_id = g.id AND g.is_delete = 0 ");
		sql.append("JOIN goods_images gi  ");
		sql.append("ON mg.goods_id = gi.goods_id AND gi.type = 0 AND gi.is_delete = 0 ");
		
		param.setString(columnCode);
		param.setString(modulesCode);
		
		return queryForInt(sql.toString(), param);
	}
}
